#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql
#打开数据库连接
conn = pymysql.connect(host = "localhost",user = "root",passwd = "330424qwerty", db = "pythonDB")
#获取游标
cursor = conn.cursor()
#创建pythonBD数据库
# cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
#先关闭游标
# cursor.close()
#再关闭数据库连接
# conn.close()
# print('创建pythonBD数据库成功')

# 创建user表
cursor.execute('drop table if exists user')
sql = """CREATE TABLE IF NOT EXISTS `user` (
    `id` int (11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
	`age` int(11) NOT NULL,
	PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cursor.execute(sql)

# insert = cursor.execute("insert into user values(1,'tom',18)")
# print('添加语句受影响的行数：',insert)

#另一种插入数据的方式，通过字符串传入值
sql = "insert into user values(%s,%s,%s)"
insert = cursor.executemany(sql,[(1,'tom',18),(3,'kongsh',20),(4,'wen',20),(5,'tom',10),(6,'test',30)])
print ('批量插入返回受影响的行数：',insert)

cursor.execute("select * from user;")
while 1:
    res = cursor.fetchone()
    if res is None:
        #表示已经取完结果集
        break
    print (res)

#更新一条数据
update = cursor.execute("update user set age=100 where name='kongsh'")
print ('修改后受影响的行数为：',update)
#查询一条数据
cursor.execute('select * from user where name="kongsh";')
print(cursor.fetchone())

#更新2条数据
sql = "update user set age=%s where name=%s"
update = cursor.executemany(sql,[(15,'kongsh'),(18,'wen')])

#更新2条数据后查询所有数据
cursor.execute("select * from user where name in ('kongsh','wen');")
print('更新后的数据为：')
for res in cursor.fetchall():
      print (res)

print ('*'*40)      
#删除2条数据
sql = "delete from user where id=%s"
cursor.executemany(sql,[(3),(4)])

#删除后查询所有数据
cursor.execute("select * from user;")
print('删除后的数据为：')
for res in cursor.fetchall():
      print (res)

#回滚事务
# conn.rollback()
# cursor.execute("select * from user;")
# print('回滚事务后的数据为：')
# for res in cursor.fetchall():
#       print (res)

cursor.close()
conn.commit()
conn.close()
print('sql执行成功')